package edu.uwm.universitydb.tests;

public class Query4 extends PerformanceTest {

	@Override
	public String getName() {
		return "Everyone who worked on a project last two years.";
	}

	@Override
	public String[] getAddIndex() {
		return new String [] {
				"create index startdate_index on project (starting_date)  using btree;",
				"create index enddate_index on project (ending_date)  using btree;",
				"create index prof_index on professor (prof_ssn)  using hash;",
				"create index stu_index on grad_student (grad_ssn)  using hash;"
		};
	}

	@Override
	String[] getDropIndex() {
		return new String [] {
				"drop index startdate_index on project",
				"drop index enddate_index on project",
				"drop index prof_index on professor",
				"drop index stu_index on grad_student"
		};
	}

	@Override
	public String getQuery() {
		return  "SELECT stu.grad_ssn " +
				"FROM grad_student stu, project pr, supervises sup  " +
				"WHERE stu.grad_ssn = sup.grad_ssn AND sup.pno = pr.pno " +
				"AND pr.ending_date > '2012-05-01'  " +
				"AND pr.ending_date < '2014-05-31'  " +
				"UNION " +
				"SELECT prof.prof_ssn " +
				"FROM professor prof, project pr, manage mng  " +
				"WHERE prof.prof_ssn = mng.prof_ssn AND mng.pno = pr.pno " +
				"AND pr.ending_date > '2012-05-01'  " +
				"AND pr.ending_date < '2014-05-31'  " +
				"UNION " +
				"SELECT prof.prof_ssn " +
				"FROM co_investigate coinv, project pr, professor prof  " +
				"WHERE coinv.pno = pr.pno AND coinv.prof_ssn = prof.prof_ssn " +
				"AND pr.ending_date > '2012-05-01'  " +
				"AND pr.ending_date < '2014-05-31' ; ";
	}
}